package com.zhyz.bookstore.dao.impl;
/**
 * 后台管理持久层
 */

import com.zhyz.bookstore.dao.ManagerDao;
import com.zhyz.bookstore.entity.Admin;
import com.zhyz.bookstore.entity.Book;
import com.zhyz.bookstore.entity.Category;
import com.zhyz.bookstore.entity.User;
import com.zhyz.bookstore.util.DBUtil;
import com.zhyz.bookstore.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ManagerDaoImpl implements ManagerDao{

    Connection conn = null;

    // 管理员登录
    @Override
    public Admin login(String username, String password) {
        String sql = "select * from administrator where admin_username=? and admin_password=?";
        Admin admin = new Admin();
        conn = JDBCUtils.getconnection();
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,username);
            prep.setString(2,password);
            resultSet = prep.executeQuery();
            if (resultSet.next()){
                admin.setUsername(resultSet.getString("admin_username"));
                admin.setPassword(resultSet.getString("admin_password"));
                admin.setName(resultSet.getString("admin_name"));
                admin.setSex(resultSet.getString("admin_Sex"));
                admin.setTel(resultSet.getString("admin_tel"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(resultSet,prep,conn);
        }
        return admin;
    }


    /**
     * 添加图书类型
     * @param category
     */
    @Override
    public void addCategory(Category category) {
        Connection conn = JDBCUtils.getconnection();
        String sql = "insert into category values(?,?,?)";
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1, category.getCategoryId());
            prep.setString(2, category.getCategoryName());
            prep.setString(3, category.getCategoryDesc());
            prep.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,prep,conn);
        }

    }

    /**
     * 所有书籍分类
     * @return
     */
    @Override
    public List<Category> getAllCategory() {
        List<Category> list = new ArrayList<Category>();
        Connection conn = JDBCUtils.getconnection();
        String sql = "select * from category";
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            resultSet = prep.executeQuery();
            while(resultSet.next()){
                Category category = new Category();
                category.setCategoryId(resultSet.getString("category_id"));
                category.setCategoryName(resultSet.getString("category_name"));
                category.setCategoryDesc(resultSet.getString("category_desc"));
                list.add(category);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(resultSet,prep,conn);
        }

        return list;
    }

    /**
     * 根据类别id查找类别对象
     * @param CategoryId
     * @return
     */
    @Override
    public Category getCategory(String CategoryId) {
        Category category = new Category();
        Connection conn = JDBCUtils.getconnection();
        String sql = "select * from category where category_id = ?";
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,CategoryId);
            resultSet = prep.executeQuery();
            if (resultSet.next()){
                category.setCategoryId(resultSet.getString("category_id"));
                category.setCategoryName(resultSet.getString("category_name"));
                category.setCategoryDesc(resultSet.getString("category_desc"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(resultSet,prep,conn);
        }

        return category;
    }

    /**
     * 修改书籍类型信息
     * @param category
     */
    @Override
    public void upCategory(Category category) {
        Connection conn = JDBCUtils.getconnection();
        String sql = "update category set category_name = ?,category_desc = ? where category_id = ?";
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1, category.getCategoryName());
            prep.setString(2, category.getCategoryDesc());
            prep.setString(3, category.getCategoryId());
            prep.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,prep,conn);
        }

    }

    /**
     * 删除书籍类型
     * @param CategoryId
     */
    @Override
    public void delCategroy(String CategoryId) {
        Connection conn = JDBCUtils.getconnection();
        String sql = "delete from category where category_id=?";
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,CategoryId);
            prep.executeUpdate();
            conn.close();
            prep.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,prep,conn);
        }
    }

    /**
     * 添加图书
     * @param book
     */
    public void addBooks(Book book){
        Connection conn = JDBCUtils.getconnection();
        String sql = "insert into bookdb values(?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement prep = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,book.getBookId());
            prep.setString(2,book.getBookName());
            prep.setString(3, book.getBookAuthor());
            prep.setString(4, book.getBookPress());
            prep.setString(5, book.getCategory().getCategoryId());
            prep.setString(6,book.getFileName());
            prep.setString(7,book.getPath());
            prep.setString(8, book.getBookDesc());
            prep.setDouble(9, book.getBookPrice());
            prep.setInt(10, book.getBookKunumber());
            prep.setInt(11, 0);
            prep.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,prep,conn);
        }
    }
//分类书籍
    public List<Book> getCategoryBook(String cid) {
        ClientDaoImpl clientDaoImpl = new ClientDaoImpl();
        return clientDaoImpl.getCategoryBook(cid);
    }

//添加工作人员
    @Override
    public void addAdmin(Admin admin) {
        try {

            Connection connection = JDBCUtils.getconnection();
            PreparedStatement prepareStatement = connection
                    .prepareStatement("insert into administrator values(?,?,?,?,?)");
            prepareStatement.setString(1, admin.getUsername());
            prepareStatement.setString(2, admin.getPassword());
            prepareStatement.setString(3, admin.getName());
            prepareStatement.setString(4, admin.getSex());
            prepareStatement.setString(5, admin.getTel());

            prepareStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据书籍ID查询书籍
     * @param BookId
     * @return
     */
    public Book getBooks(String BookId){
        Book book = new Book();
        Connection conn = JDBCUtils.getconnection();
        String sql = "select * from bookdb where book_id = ?";
        PreparedStatement prep = null;
        ResultSet resultSet = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,BookId);
            resultSet = prep.executeQuery();
            if (resultSet.next()){
                book.setBookId(resultSet.getString("book_id"));
                book.setBookName(resultSet.getString("book_name"));
                book.setBookAuthor(resultSet.getString("book_author"));
                book.setBookPress(resultSet.getString("book_press"));
                Category category = getCategory(resultSet.getString("book_category"));
                book.setCategory(category);
                book.setFileName(resultSet.getString("filename"));
                book.setPath(resultSet.getString("path"));
                book.setBookDesc(resultSet.getString("book_price"));
                book.setBookPrice(resultSet.getDouble("book_price"));
                book.setBookKunumber(resultSet.getInt("book_kunumber"));
                book.setBookXiaoNumber(resultSet.getInt("book_xiaonumber"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(resultSet,prep,conn);
        }
        return book;
    }

    /**
     * 修改图书信息
     * @param
     */
    public void upBook(String BookId, String BookName, String BookAuthor, String BookPress, String BookDesc,
                       double BookPrice, String BookKunumber){
        Connection conn = JDBCUtils.getconnection();
        PreparedStatement prep = null;
        String sql = "update bookdb set book_name=?,book_author=?,book_press=?,book_desc=?,book_price=?,book_kunumber=? where book_id = ?";
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,BookName);
            prep.setString(2,BookAuthor);
            prep.setString(3,BookPress);
            prep.setString(4,BookDesc);
            prep.setDouble(5,BookPrice);
            prep.setString(7,BookKunumber);
            prep.setString(7,BookId);
            prep.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,prep,conn);
        }
    }

    /**
     * 删除书籍信息
     * @param
     */
    public void delBook(String BookId){
        Connection conn = JDBCUtils.getconnection();
        String sql = "delete from bookdb where book_id = ?";
        PreparedStatement prep = null;
        try {
            prep = conn.prepareStatement(sql);
            prep.setString(1,BookId);
            prep.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(null,prep,conn);
        }
    }

    //管理员登录密码修改
    @Override
    public void managerPassword(Admin admin) {
        try {
            Connection connection = JDBCUtils.getconnection();
            PreparedStatement prepareStatement = connection
                    .prepareStatement("update administrator set admin_password=? where admin_username=?");
            prepareStatement.setString(1, admin.getPassword());
            prepareStatement.setString(2, admin.getUsername());
            prepareStatement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //修改个人信息
    @Override
    public void managerInformation(Admin admin) {
        try {
            Connection connection = JDBCUtils.getconnection();
            PreparedStatement prepareStatement = connection.prepareStatement(
                    "update administrator set admin_name=?, admin_sex=?, admin_tel=? where admin_username=? ");
            prepareStatement.setString(1, admin.getName());
            prepareStatement.setString(2, admin.getSex());
            prepareStatement.setString(3, admin.getTel());
            prepareStatement.setString(4, admin.getUsername());
            prepareStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //用户信息管理
    @Override
    public List<User> findUsers() {
        try {
            Connection connection = JDBCUtils.getconnection();
            PreparedStatement prepareStatement = connection.prepareStatement("select * from user");
            ResultSet rs = prepareStatement.executeQuery();
            List<User> users = new ArrayList<User>();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getString("user_id"));
                user.setUsername(rs.getString("user_username"));
                user.setPassword(rs.getString("user_password"));
                user.setName(rs.getString("user_name"));
                user.setSex(rs.getString("user_sex"));
                user.setTel(rs.getString("user_tel"));
                user.setAddress(rs.getString("user_address"));
                users.add(user);
            }
            return users;
        } catch (Exception e) {
            e.printStackTrace();
        }
        throw new RuntimeException();
    }


    @Override
    public void delUser(String userId) {
        Connection conn = JDBCUtils.getconnection();
        PreparedStatement pstat = null;
        String sql = "delete from user where user_id = ?";
        try {
            pstat = conn.prepareStatement(sql);
            pstat.setString(1,userId);
            pstat.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(null,pstat,conn);
        }

    }

}
